/**************************************************************************************
 *
 * @(#) HomePageBiz.java
 * @Package com.jgdt.jgdtkasannong_university_gene_backend.biz.content.impl
 **************************************************************************************/
package com.jgdt.jgdtkasannong_university_gene_backend.biz.content.impl;


import cn.hutool.core.collection.CollectionUtil;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.jgdt.jgdtkasannong_university_gene_backend.biz.content.MenuContentBiz;
import com.jgdt.jgdtkasannong_university_gene_backend.biz.content.TemplatetBiz;
import com.jgdt.jgdtkasannong_university_gene_backend.common.enums.BusinessCodeEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.common.example.custom.BusinessException;
import com.jgdt.jgdtkasannong_university_gene_backend.config.enums.FilePatternsEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.config.enums.SysValueSetEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.config.enums.template.TemplateCodeEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.config.enums.template.TemplateContentEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.config.enums.template.TemplateCopywritingEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.dto.BaseImgDataDto;
import com.jgdt.jgdtkasannong_university_gene_backend.dto.ContenMediaDto;
import com.jgdt.jgdtkasannong_university_gene_backend.dto.UpdateContentDto;
import com.jgdt.jgdtkasannong_university_gene_backend.entity.base.BaseInfo;
import com.jgdt.jgdtkasannong_university_gene_backend.entity.content.DataTemplate;
import com.jgdt.jgdtkasannong_university_gene_backend.entity.content.Media;
import com.jgdt.jgdtkasannong_university_gene_backend.enums.content.BizTypeEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.enums.content.MediaTypeEnums;
import com.jgdt.jgdtkasannong_university_gene_backend.service.base.BaseInfoService;
import com.jgdt.jgdtkasannong_university_gene_backend.service.content.ContentGeneralService;
import com.jgdt.jgdtkasannong_university_gene_backend.service.content.MediaService;
import com.jgdt.jgdtkasannong_university_gene_backend.service.content.SysMenuService;
import com.jgdt.jgdtkasannong_university_gene_backend.service.sys.SysValueSetService;
import com.jgdt.jgdtkasannong_university_gene_backend.utils.ListUtil;
import com.jgdt.jgdtkasannong_university_gene_backend.utils.excel.ExcelImageExtractor;
import com.jgdt.jgdtkasannong_university_gene_backend.utils.excel.ExcelImageUtils;
import com.jgdt.jgdtkasannong_university_gene_backend.utils.excel.ImageUtils;
import com.jgdt.jgdtkasannong_university_gene_backend.utils.file.FileUtil;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.base.CheckimportVo;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.base.BaseImgData;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.base.BaseTemplate;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_achievements.AchievementIntroduce;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_achievements.RepresentativeAchievement;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_achievements.ResourceProtection;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_achievements.data.AchievementDataVo;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_team.KeyMembers;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_team.ResearchteamIntroduce;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_team.TeamLeader;
import com.jgdt.jgdtkasannong_university_gene_backend.vo.content.excel.research_team.data.TeamDataVo;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.FileSystemResource;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
import org.apache.poi.ss.usermodel.*;

import java.util.ArrayList;
import java.util.List;



/**
 * @description: 菜单内容-biz实现
 * @author: huangAn
 * @createDate: 2025/04/24
 */
@Service
@Slf4j
public class TemplateBizImpl implements TemplatetBiz {

    private static final String IMAGE_UPLOAD_DIR = "D:\\testImg";
    @Autowired
    private SysMenuService sysMenuService;
    @Autowired
    private MenuContentBiz menuContentBiz;
    @Autowired
    private ContentGeneralService contentGeneralService;
    @Autowired
    private BaseInfoService baseInfoService;
    @Autowired
    private SysValueSetService sysValueSetService;
    @Autowired
    private MediaService mediaService;

    public String getMaxFileSize() {
        return maxFileSize;
    }

    @Value("${spring.servlet.multipart.max-file-size}")
    private String maxFileSize;



    @Override
    public void downloadByMenu(HttpServletResponse response, Integer menuId) {
        try {
            //校验菜单是否可以下载模板
            DataTemplate dataTemplate = sysMenuService.queryDataTemplateByMenuId(menuId);
            if (ObjectUtils.isNull(dataTemplate)) {
                throw new BusinessException(BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getKey(), BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getMsg());
            }
            //校验模板
            if(StringUtils.isEmpty(dataTemplate.getTemplateName())){
                throw new BusinessException(BusinessCodeEnums.TEMPLATET_CONFIG_INCOMPLETE.getKey(), BusinessCodeEnums.TEMPLATET_CONFIG_INCOMPLETE.getMsg());
            }
            String templateCode=dataTemplate.getTemplateCode();
            //根据菜单构建 workbook 对象
            if (TemplateCodeEnums.RESEARCHTEAM.getKey().equals(templateCode)) {
                //科研团队 构建模板数据
                researchteamIntroduceXSSFWorkbook(response, menuId,dataTemplate);
            } else if (TemplateCodeEnums.RESEARCH_ACHIEVEMENTS.getKey().equals(templateCode)) {
                //科研成果 构建模板数据
                researchAchievementsXSSFWorkbook(response, menuId,dataTemplate);
            } else if (TemplateCodeEnums.PLACE_BASE.getKey().equals(templateCode)) {
                //地方猪种源基地 构建模板数据
                baseXSSFWorkbook(response, menuId,dataTemplate);
            } else if (TemplateCodeEnums.LEAN_MEAT_BASE.getKey().equals(templateCode)) {
                //瘦肉型猪种源基地 构建模板数据
                baseXSSFWorkbook(response, menuId,dataTemplate);
            } else if (TemplateCodeEnums.BLACK_PIG_BASE.getKey().equals(templateCode)) {
                //黑猪盟养殖基地 构建模板数据
                baseXSSFWorkbook(response, menuId,dataTemplate);
            } else if (TemplateCodeEnums.INNOVATE_BASE.getKey().equals(templateCode)) {
                //猪种质创新基地 构建模板数据
                baseXSSFWorkbook(response, menuId,dataTemplate);
            } else {
                throw new BusinessException(BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getKey(), BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getMsg());
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public TeamDataVo queryTeamLeader(Integer menuId) {
        TeamDataVo teamDataVo=new TeamDataVo();
        //调用该菜单下所有内容信息
        List<Integer> menuIds=new ArrayList<>();
        menuIds.add(TemplateContentEnums.RESEARCHTEAM_INTRODUCE.getMenuid());
        menuIds.add(TemplateContentEnums.RESEARCHTEAM_TEAMLEADER.getMenuid());
        menuIds.add(TemplateContentEnums.RESEARCHTEAM_KEYMEMBERS.getMenuid());
        List<ContenMediaDto>  contenMediaDtoList=contentGeneralService.contenMediaByMenuIds(menuIds);

        Map<Integer,List<ContenMediaDto>> contenMediaDtoListMap=contenMediaDtoList.stream().collect(Collectors.groupingBy(ContenMediaDto::getMenuId));
        //科研简介
        List<ContenMediaDto> introduceList=contenMediaDtoListMap.get(TemplateContentEnums.RESEARCHTEAM_INTRODUCE.getMenuid());
        if (CollectionUtil.isNotEmpty(introduceList)){
            ResearchteamIntroduce researchteamIntroduce=new ResearchteamIntroduce();
            researchteamIntroduce.setIntroduce(introduceList.get(0).getContent1());
            teamDataVo.setResearchteamIntroduce(researchteamIntroduce);
        }
        //团队带头人
        List<ContenMediaDto>  teamleaderList=contenMediaDtoListMap.get(TemplateContentEnums.RESEARCHTEAM_TEAMLEADER.getMenuid());
        List<TeamLeader> teamLeaders=new ArrayList<>();
        if(CollectionUtil.isNotEmpty(teamleaderList)){
            for(ContenMediaDto contenMediaDto:teamleaderList){
                TeamLeader teamLeader=new TeamLeader();
                teamLeader.setCode(String.valueOf(contenMediaDto.getGeneralId()));
                teamLeader.setName(contenMediaDto.getContent1());
                teamLeader.setTitle(contenMediaDto.getContent2());
                teamLeader.setIntroduce(contenMediaDto.getContent3());
                teamLeader.setImg(contenMediaDto.getImageUrl());
                teamLeaders.add(teamLeader);
            }
            teamDataVo.setTeamLeaders(teamLeaders);
        }
        //骨干成员
        List<ContenMediaDto>  keyMembersList=contenMediaDtoListMap.get(TemplateContentEnums.RESEARCHTEAM_KEYMEMBERS.getMenuid());
        List<KeyMembers> keyMemberss=new ArrayList<>();
        if(CollectionUtil.isNotEmpty(keyMembersList)){
            for(ContenMediaDto contenMediaDto:keyMembersList){
                KeyMembers keyMembers=new KeyMembers();
                keyMembers.setCode(String.valueOf(contenMediaDto.getGeneralId()));
                keyMembers.setName(contenMediaDto.getContent1());
                keyMembers.setTitle(contenMediaDto.getContent2());
                keyMembers.setIntroduce(contenMediaDto.getContent3());
                keyMembers.setImg(contenMediaDto.getImageUrl());
                keyMemberss.add(keyMembers);
            }
        }
        teamDataVo.setKeyMembers(keyMemberss);
        return teamDataVo;
    }

    @Override
    public AchievementDataVo queryAchievement(Integer menuId) {
        AchievementDataVo achievementDataVo = new AchievementDataVo();
        //调用该菜单下所有内容信息
        List<Integer> menuIds = new ArrayList<>();
        menuIds.add(TemplateContentEnums.ACHIEVEMENTS_INTRODUCE.getMenuid());

        menuIds.add(TemplateContentEnums.ACHIEVEMENTS_REPRESENTATIVE.getMenuid());
        menuIds.add(TemplateContentEnums.REPRESENTATIVENESS_REPRESENTATIVE.getMenuid());
        menuIds.add(TemplateContentEnums.PATENTABILITY_REPRESENTATIVE.getMenuid());
        menuIds.add(TemplateContentEnums.PAPER_REPRESENTATIVE.getMenuid());

        menuIds.add(TemplateContentEnums.ACHIEVEMENTS_RESOURCES.getMenuid());
        List<ContenMediaDto> contenMediaDtoList = contentGeneralService.contenMediaByMenuIds(menuIds);

        Map<Integer, List<ContenMediaDto>> contenMediaDtoListMap = contenMediaDtoList.stream().collect(Collectors.groupingBy(ContenMediaDto::getMenuId));
        //科研成果-成果简介
        List<ContenMediaDto> introduceList = contenMediaDtoListMap.get(TemplateContentEnums.ACHIEVEMENTS_INTRODUCE.getMenuid());
        if (CollectionUtil.isNotEmpty(introduceList)) {
            AchievementIntroduce achievementIntroduce = new AchievementIntroduce();
            achievementIntroduce.setIntroduce(introduceList.get(0).getContent1());
            achievementDataVo.setAchievementIntroduce(achievementIntroduce);
        }
        //初始化数据
        List<ContenMediaDto> achievementsIntroduceList = contenMediaDtoListMap.get(TemplateContentEnums.ACHIEVEMENTS_REPRESENTATIVE.getMenuid());
        List<ContenMediaDto> representativenessIntroduceList = contenMediaDtoListMap.get(TemplateContentEnums.REPRESENTATIVENESS_REPRESENTATIVE.getMenuid());
        List<ContenMediaDto> patentabilityIntroduceList = contenMediaDtoListMap.get(TemplateContentEnums.PATENTABILITY_REPRESENTATIVE.getMenuid());
        List<ContenMediaDto> paperIntroduceList = contenMediaDtoListMap.get(TemplateContentEnums.PAPER_REPRESENTATIVE.getMenuid());
        //筛选最长的list
        List<ContenMediaDto> longestList = Arrays.asList(achievementsIntroduceList, representativenessIntroduceList, patentabilityIntroduceList, paperIntroduceList)
                .stream()
                .max(Comparator.comparingInt(List::size))
                .orElse(null);
        List<RepresentativeAchievement> representativeAchievementList = new ArrayList<>();
        for (ContenMediaDto contenMediaDto : longestList) {
            RepresentativeAchievement representativeAchievement = new RepresentativeAchievement();
            representativeAchievementList.add(representativeAchievement);
        }
        //科研成果-获奖成果
        for (int i = 0; i < achievementsIntroduceList.size(); i++) {
            ContenMediaDto contenMediaDto = achievementsIntroduceList.get(i);
            representativeAchievementList.get(i).setCode(String.valueOf(contenMediaDto.getGeneralId()));
            representativeAchievementList.get(i).setImg(contenMediaDto.getImageUrl());
        }
        //科研成果-代表性成果
        for (int i = 0; i < representativenessIntroduceList.size(); i++) {
            ContenMediaDto contenMediaDto = representativenessIntroduceList.get(i);
            representativeAchievementList.get(i).setStandardCode(String.valueOf(contenMediaDto.getGeneralId()));
            representativeAchievementList.get(i).setStandardImg(contenMediaDto.getImageUrl());
        }
        //科研成果-专利性成果
        for (int i = 0; i < patentabilityIntroduceList.size(); i++) {
            ContenMediaDto contenMediaDto = patentabilityIntroduceList.get(i);
            representativeAchievementList.get(i).setPatentCode(String.valueOf(contenMediaDto.getGeneralId()));
            representativeAchievementList.get(i).setPatentImg(contenMediaDto.getImageUrl());
        }
        //科研成果-论文性成果
        for (int i = 0; i < paperIntroduceList.size(); i++) {
            ContenMediaDto contenMediaDto = paperIntroduceList.get(i);
            representativeAchievementList.get(i).setPaperCode((String.valueOf(contenMediaDto.getGeneralId())));
            representativeAchievementList.get(i).setPaperImg(contenMediaDto.getImageUrl());
        }
        achievementDataVo.setRepresentativeAchievementList(representativeAchievementList);

        //资源保护
        List<ContenMediaDto> achievementsList = contenMediaDtoListMap.get(TemplateContentEnums.ACHIEVEMENTS_RESOURCES.getMenuid());
        List<ResourceProtection> resourceProtectionList = new ArrayList<>();
        for (ContenMediaDto contenMediaDto : achievementsList) {
            ResourceProtection resourceProtection = new ResourceProtection();
            resourceProtection.setCode(String.valueOf(contenMediaDto.getGeneralId()));
            resourceProtection.setImg(contenMediaDto.getImageUrl());
            resourceProtection.setTitle(contenMediaDto.getContent1());
            resourceProtection.setIntroduce(contenMediaDto.getContent2());
            resourceProtectionList.add(resourceProtection);
        }
        achievementDataVo.setResourceProtectionList(resourceProtectionList);
        return achievementDataVo;
    }

    @Override
    public CheckimportVo checkImportData(MultipartFile file, Integer menuId) {
        CheckimportVo checkimportVo = new CheckimportVo();
        checkimportVo.setSuccessBool(true);
        List<String> errList=new ArrayList<>();
        //校验文件基础信息
        String err = this.checkFile(file);
        if (StringUtils.isNotEmpty(err)) {
            errList.add(err);
            checkimportVo.setSuccessBool(false);
            checkimportVo.setErrList(errList);
            return checkimportVo;
        }
        //分类校验
        if (TemplateCodeEnums.RESEARCHTEAM.getId().equals(menuId)) {
            try {
                //根据Excel表格数据 构建查询返回结构对象
                TeamDataVo teamDataVo = constructKeyTeamDataVo(file, menuId);
//                //根据查询返回结构对象 构建db操作对象
//                List<UpdateContentDto> updateContentDtos = menuContentBiz.teamDataVoChangeDb(teamDataVo, menuId);
                //校验
                checkTeamDataVo(teamDataVo,errList);
            } catch (Exception e) {
                checkimportVo.setSuccessBool(false);
                errList.add(e.getMessage());
                checkimportVo.setErrList(errList);
                return checkimportVo;
            }
        } else if (TemplateCodeEnums.RESEARCH_ACHIEVEMENTS.getId().equals(menuId)) {
            try {
                //根据Excel表格数据 构建查询返回结构对象
                AchievementDataVo achievementDataVo = constructAchievementVo(file, menuId);
                //根据查询返回结构对象 构建db操作对象,此对象可以做具体校验
//                List<UpdateContentDto> updateContentDtos = menuContentBiz.achievementVoChangeDb(achievementDataVo, menuId);
                checkAchievementDataVo(achievementDataVo,errList);
            } catch (Exception e) {
                checkimportVo.setSuccessBool(false);
                errList.add(e.getMessage());
                checkimportVo.setErrList(errList);
                return checkimportVo;
            }

        } else if (TemplateCodeEnums.PLACE_BASE.getId().equals(menuId) || TemplateCodeEnums.LEAN_MEAT_BASE.getId().equals(menuId)
                || TemplateCodeEnums.BLACK_PIG_BASE.getId().equals(menuId) || TemplateCodeEnums.INNOVATE_BASE.getId().equals(menuId)) {
            try {
                //根据Excel表格数据 构建查询返回结构对象,此对象可以做具体校验
                List<BaseImgData> baseImgDats = constructBaseVo(file, menuId);
//                BaseImgDataDto baseImgDataDto = menuContentBiz.baseImgChangeDb(baseImgDats, menuId);
                checkBaseImgDats(baseImgDats,errList,menuId);
            } catch (Exception e) {
                checkimportVo.setSuccessBool(false);
                checkimportVo.setErrList(Arrays.asList(e.getMessage()));
                return checkimportVo;
            }
        } else {
            throw new BusinessException(BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getKey(), BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getMsg());
        }
        if(CollectionUtil.isNotEmpty(errList)){
            checkimportVo.setSuccessBool(false);
            checkimportVo.setErrList(errList);
        }
        return checkimportVo;
    }

    /**
     * 校验对象
     * @param baseImgDats
     * @param errList
     * @param menuId
     */
    private void checkBaseImgDats(List<BaseImgData> baseImgDats, List<String> errList, Integer menuId) {
        if(CollectionUtil.isEmpty(baseImgDats)){
            errList.add("无数据，请检查！");
            return;
        }
        List<Integer> codeList=baseImgDats.stream().map(BaseImgData::getCode).collect(Collectors.toList());
        List<BaseTemplate> baseTemplateList=baseInfoService.queryBaseInfoImg(menuId);
        if (CollectionUtil.isEmpty(codeList)) {
            errList.add("无数据，请检查！");
            return;
        }
        if (CollectionUtil.isEmpty(baseTemplateList)) {
            errList.add("无数据，请检查！");
            return;
        }
        Map<String,BaseTemplate> baseTemplateListMap=baseTemplateList.stream().collect(Collectors.toMap(BaseTemplate::getCode, Function.identity()));
        for(Integer code:codeList){
            BaseTemplate baseTemplate= baseTemplateListMap.get(String.valueOf(code));
            if(ObjectUtils.isEmpty(baseTemplate)){
                errList.add("基地编码:"+code+",不存在");
            }
        }
        return;
    }

    /**
     * 校验对象
     *
     * @param achievementDataVo
     */
    private void checkAchievementDataVo(AchievementDataVo achievementDataVo, List<String> errList) {
        if (ObjectUtils.isEmpty(achievementDataVo)) {
            errList.add("无数据，请检查！");
            return;
        }
        //科研成果-成果简介
        if(ObjectUtils.isEmpty(achievementDataVo.getAchievementIntroduce())){
            errList.add("科研成果-成果简介无数据，请检查！");
            return;
        }
        if(ObjectUtils.isEmpty(achievementDataVo.getAchievementIntroduce().getIntroduce())){
            errList.add("科研成果-成果简介无数据，请检查！");
            return;
        }
        List<RepresentativeAchievement> representativeAchievementList=achievementDataVo.getRepresentativeAchievementList();
        if(CollectionUtil.isEmpty(representativeAchievementList)){
            errList.add("科研成果-代表性成果无数据，请检查！");
            return;
        }
        List<ResourceProtection> resourceProtectionList=achievementDataVo.getResourceProtectionList();
        if(CollectionUtil.isEmpty(resourceProtectionList)){
            errList.add("科研成果-资源保护无数据，请检查！");
            return;
        }
        List<String> resourceProtectionCodeList=resourceProtectionList.stream().map(ResourceProtection::getCode).collect(Collectors.toList());
        if(resourceProtectionCodeList.size()>TemplateContentEnums.ACHIEVEMENTS_RESOURCES.getMaxNumber()){
            errList.add("科研成果-资源保护数据最大不能超过"+TemplateContentEnums.ACHIEVEMENTS_RESOURCES.getMaxNumber()+"条数据，请检查！");
        }
        //获奖成果
        List<String> codeList=representativeAchievementList.stream().map(RepresentativeAchievement::getCode).collect(Collectors.toList());
        if(codeList.size()>TemplateContentEnums.ACHIEVEMENTS_REPRESENTATIVE.getMaxNumber()){
            errList.add("科研成果-获奖成果数据最大不能超过"+TemplateContentEnums.ACHIEVEMENTS_REPRESENTATIVE.getMaxNumber()+"条数据，请检查！");
        }
        //代表性标准图片
        List<String> standardCodeList=representativeAchievementList.stream().map(RepresentativeAchievement::getStandardCode).collect(Collectors.toList());
        if(standardCodeList.size()>TemplateContentEnums.REPRESENTATIVENESS_REPRESENTATIVE.getMaxNumber()){
            errList.add("科研成果-代表性标准图片数据最大不能超过"+TemplateContentEnums.REPRESENTATIVENESS_REPRESENTATIVE.getMaxNumber()+"条数据，请检查！");
        }
        //专利图片
        List<String> patentCodeList=representativeAchievementList.stream().map(RepresentativeAchievement::getPatentCode).collect(Collectors.toList());
        if(patentCodeList.size()>TemplateContentEnums.PATENTABILITY_REPRESENTATIVE.getMaxNumber()){
            errList.add("科研成果-专利图片图片数据最大不能超过"+TemplateContentEnums.PATENTABILITY_REPRESENTATIVE.getMaxNumber()+"条数据，请检查！");
        }
        //代表性论文图片
        List<String> paperCodeList=representativeAchievementList.stream().map(RepresentativeAchievement::getPaperCode).collect(Collectors.toList());
        if(paperCodeList.size()>TemplateContentEnums.PAPER_REPRESENTATIVE.getMaxNumber()){
            errList.add("科研成果-代表性论文图片数据最大不能超过"+TemplateContentEnums.PAPER_REPRESENTATIVE.getMaxNumber()+"条数据，请检查！");
        }
    }

    /**
     * 校验对象
     *
     * @param teamDataVo
     */
    private void checkTeamDataVo(TeamDataVo teamDataVo, List<String> errList) {
        if (ObjectUtils.isEmpty(teamDataVo)) {
            errList.add("无数据，请检查！");
            return;
        }
        if (ObjectUtils.isEmpty(teamDataVo.getResearchteamIntroduce())) {
            errList.add("科研团队-介绍无数据，请检查！");
            return;
        }
        if (ObjectUtils.isEmpty(teamDataVo.getResearchteamIntroduce().getIntroduce())) {
            errList.add("科研团队-介绍无数据，请检查！");
            return;
        }
        //科研团队
        List<KeyMembers> keyMemberList = teamDataVo.getKeyMembers();
        if (CollectionUtil.isEmpty(keyMemberList)) {
            errList.add("科研团队-骨干成员无数据，请检查！");
            return;
        }
        //数量限制
        Integer keyMemberMaxNumber = TemplateContentEnums.RESEARCHTEAM_KEYMEMBERS.getMaxNumber();
        if (keyMemberMaxNumber < keyMemberList.size()) {
            errList.add("科研团队-骨干成员数据最大不能超过" + keyMemberMaxNumber + "条数据，请检查");
            return;
        }
        //团队带头人
        List<TeamLeader> teamLeadersList =teamDataVo.getTeamLeaders();
        if(CollectionUtil.isEmpty(teamLeadersList)){
            errList.add("科研团队-团队带头人无数据，请检查！");
            return;
        }
        Integer teamMaxNumber = TemplateContentEnums.RESEARCHTEAM_TEAMLEADER.getMaxNumber();
        if (teamMaxNumber < teamLeadersList.size()) {
            errList.add("科研团队-团队带头人数据最大不能超过" + teamMaxNumber + "条数据，请检查");
            return;
        }
    }

    @Override
    public void importData(MultipartFile file, Integer menuId) {
        //this 自调用异步失效，即为同步处理
        this.importDataAsync(file,menuId);
    }

    @Override
    @Async("taskExecutor")
    public void importDataAsync(MultipartFile file, Integer menuId) {
        String tempImgCatalogue=sysValueSetService.queryPriorityValueByCode(SysValueSetEnums.TEMP_IMG_CATALOGUE.getCode());
        log.info("tempImgCatalogue:"+tempImgCatalogue);
        String tempImgCataloguePrefixHead=sysValueSetService.queryPriorityValueByCode(SysValueSetEnums.TEMP_IMG_CATALOGUE_PREFIX_HEAD.getCode());
        log.info("tempImgCataloguePrefixHead:"+tempImgCataloguePrefixHead);
        String tempImgUrl=tempImgCataloguePrefixHead+tempImgCatalogue;
        log.info("tempImgUrl:"+tempImgUrl);
        if (TemplateCodeEnums.RESEARCHTEAM.getId().equals(menuId)) {
            //清空所有相关图片信息
            FileUtil.deleteAllFilesInDir(tempImgUrl+TemplateCodeEnums.getById(menuId).getImportUrl());
            //根据Excel表格数据 构建查询返回结构对象
            TeamDataVo teamDataVo = constructKeyTeamDataVo(file, menuId);
            //根据查询返回结构对象 构建db操作对象
            List<UpdateContentDto> updateContentDtos = menuContentBiz.teamDataVoChangeDb(teamDataVo, menuId);
            menuContentBiz.updateContentDto(updateContentDtos);
        } else if (TemplateCodeEnums.RESEARCH_ACHIEVEMENTS.getId().equals(menuId)) {
            //清空所有相关图片信息
            FileUtil.deleteAllFilesInDir(tempImgUrl+ TemplateCodeEnums.getById(menuId).getImportUrl());
            //根据Excel表格数据 构建查询返回结构对象
            AchievementDataVo achievementDataVo=constructAchievementVo(file, menuId);
            //根据查询返回结构对象 构建db操作对象
            List<UpdateContentDto> updateContentDtos = menuContentBiz.achievementVoChangeDb(achievementDataVo, menuId);
            menuContentBiz.updateContentDto(updateContentDtos);
        }else if(TemplateCodeEnums.PLACE_BASE.getId().equals(menuId) || TemplateCodeEnums.LEAN_MEAT_BASE.getId().equals(menuId)
                || TemplateCodeEnums.BLACK_PIG_BASE.getId().equals(menuId) ||TemplateCodeEnums.INNOVATE_BASE.getId().equals(menuId)){
            //清空所有相关图片信息
//            FileUtil.deleteAllFilesInDir(tempImgUrl+ TemplateCodeEnums.getById(menuId).getImportUrl());
            //根据Excel表格数据 构建查询返回结构对象
            List<BaseImgData> baseImgDats=constructBaseVo(file, menuId);
            List<String> deleteImgNames=fileImgName(baseImgDats,menuId);
            if (CollectionUtil.isNotEmpty(deleteImgNames)){
                //清空指定 目录下指定文件
                FileUtil.delContentsPathByFileName(tempImgUrl+TemplateCodeEnums.getById(menuId).getImportUrl(),deleteImgNames);
            }
//            //根据Excel表格数据 构建查询返回结构对象
//            List<BaseImgData> baseImgDats=constructBaseVo(file, menuId);
            BaseImgDataDto baseImgDataDto=menuContentBiz.baseImgChangeDb(baseImgDats,menuId);
            baseInfoService.saveOrUpdateImg(baseImgDataDto);
        }else{
            throw new BusinessException(BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getKey(), BusinessCodeEnums.BASE_MENU_NO_TEMPLATE_ERR.getMsg());
        }
    }

    /**
     * 筛选未做改动的 所有图片信息
     *
     * @param baseImgDats
     * @param menuId
     * @return
     */
    private List<String> fileImgName(List<BaseImgData> baseImgDats, Integer menuId) {
        List<String> imgList=new ArrayList<>();
        //查询所有相关的基地数据
        List<BaseInfo> dbBaseInfoList = menuContentBiz.checkBaseInfoByMenuId(menuId);
        if (CollectionUtil.isNotEmpty(dbBaseInfoList) && CollectionUtil.isNotEmpty(baseImgDats)) {
            List<Integer> baseIds=dbBaseInfoList.stream().map(BaseInfo::getId).collect(Collectors.toList());
            Map<Integer,BaseInfo> dbBaseInfoListMap=dbBaseInfoList.stream().collect(Collectors.toMap(BaseInfo::getId,Function.identity()));
            List<Integer> dbBaseInfoIds=dbBaseInfoList.stream().map(BaseInfo::getId).collect(Collectors.toList());
            List<Integer> baseImgDatIds=baseImgDats.stream().map(BaseImgData::getCode).collect(Collectors.toList());
            //查询对应图片信息
            List<Media> mediaList=mediaService.queryByBaseId(baseIds,MediaTypeEnums.IMG.getKey(), BizTypeEnums.BASE.getKey());
            Map<Integer,Media> mediaListMap=new HashMap<>();
            if(CollectionUtil.isNotEmpty(mediaList)){
                mediaListMap=mediaList.stream().collect(Collectors.toMap(Media::getBaseId,Function.identity()));
            }

            //取交集idList
            Set<Integer> intersectionList=  ListUtil.getUniqueIntersection(dbBaseInfoIds,baseImgDatIds);
            if(CollectionUtil.isNotEmpty(intersectionList)){
                for(Integer id:intersectionList){
                    //截取 文件名
                    String slipIndex=null;
                    String code= mediaService.getFilePatternsCode();
                    if(FilePatternsEnums.WIDOS.getCode().equals(code)){
                        slipIndex= FilePatternsEnums.getEnumsByCode(FilePatternsEnums.WIDOS.getCode()).getSlipIndex();
                    }else if(FilePatternsEnums.LINX.getCode().equals(code)){
                        slipIndex=  FilePatternsEnums.getEnumsByCode(FilePatternsEnums.LINX.getCode()).getSlipIndex();
                    }else{
                        log.warn("getAfterLastSlash warn:配置文件FilePatternsEnums.java有问题");
                    }
                    if(ObjectUtils.isNotEmpty(mediaListMap.get(id))){
                        String imgUrl=mediaListMap.get(id).getImageUrl();
                        if(StringUtils.isNotEmpty(imgUrl)){
                            String imgName= ImageUtils.getAfterLastSlash(imgUrl,slipIndex,code);
                            imgList.add(imgName);
                        }
                    }
                }
            }
        }
        return imgList;
    }

    @Override
    public String checkFile(MultipartFile file) {
        //验证文件
        // 1. 验证文件是否为空
        if (file.isEmpty()) {
            return "请选择要上传的文件";
        }
        // 2. 验证文件大小
        int maxFileSizeValue = Integer.parseInt(this.getMaxFileSize().substring(0, this.getMaxFileSize().length() - 2));
        long maxFileSize = maxFileSizeValue * 1024 * 1024; //  MB
        if (file.getSize() > maxFileSize) {
            return "文件大小不能超过"+maxFileSizeValue+"MB";
        }
        return null;
    }



    /**
     * 基地数据构建
     * @param file
     * @param menuId
     * @return
     */
    private List<BaseImgData> constructBaseVo(MultipartFile file, Integer menuId) {
        List<BaseImgData> baseImgDataList = new ArrayList<>();
        try {
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            //解析构建 shell1
            Sheet sheet1 = workbook.getSheetAt(0);
            // 全局映射表：Map<行号, Map<列号, PictureData>>
            Map<Integer, Map<Integer, PictureData>> imageAnchorMap2 = new HashMap<>();
            imageAnchorMap2 = ExcelImageExtractor.buildImageAnchorMap(sheet1);
//            List<BaseImgData> teamLeaderList = new ArrayList<>();
            for (Row row : sheet1) {
                BaseImgData baseImgData = new BaseImgData();
                if (row.getRowNum() == 0) {
                    continue; // 跳过第一行
                }
                if (row == null) {
                    continue;
                }
                String theFirstColumn = ExcelImageUtils.getCellValueAsString(row.getCell(0));
                if (theFirstColumn.length() >= 2 && theFirstColumn.substring(0, 2).equals(TemplateCopywritingEnums.MSG.getMsg().substring(0, 2))) {
                    //遇到 注意文案
                    continue;
                }
                //基地数据不可新增 第一类为空的数据直接返回
                if(StringUtils.isEmpty(theFirstColumn)){
                    continue;
                }
                if (StringUtils.isNotEmpty(theFirstColumn)) {
                    baseImgData.setCode(Integer.parseInt(theFirstColumn)); //第1列
                }
                baseImgData.setBaseName(ExcelImageUtils.getCellValueAsString(row.getCell(1)));  //第2列
                baseImgData.setVarietyName(ExcelImageUtils.getCellValueAsString(row.getCell(2))); //第3列
                // 提取图片并存储
                PictureData picture = imageAnchorMap2.get(row.getRowNum()).get(3); // 第4列
                if (ObjectUtils.isNotEmpty(picture)) {
                    //保存图片到服务器
                    String imageUrl = this.saveImageToServer(picture.getData(), menuId);
                    baseImgData.setBaseImg(imageUrl);
                }
                baseImgData.setBaseInfo(ExcelImageUtils.getCellValueAsString(row.getCell(4)));  // 第5列
                baseImgDataList.add(baseImgData);
            }
        } catch (Exception e) {
            log.error("constructKeyTeamDataVo,err:" + e.getMessage());
            throw new BusinessException(BusinessCodeEnums.BASE_COMMENT_FORMAT_ERR.getKey(), BusinessCodeEnums.BASE_COMMENT_FORMAT_ERR.getMsg());
        }
        return baseImgDataList;
    }

    /**
     * 科研成果填充数据
     * @param file
     * @param menuId
     * @return
     * @throws IOException
     */
    private AchievementDataVo constructAchievementVo(MultipartFile file, Integer menuId) {
        AchievementDataVo achievementDataVo = new AchievementDataVo();
        try {
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            //解析构建 shell1
            Sheet sheet1 = workbook.getSheetAt(0);
            AchievementIntroduce achievementIntroduce = new AchievementIntroduce();
            //第1列，第2行
            achievementIntroduce.setIntroduce(sheet1.getRow(1).getCell(0).getStringCellValue());
            achievementDataVo.setAchievementIntroduce(achievementIntroduce);

            //解析构建 shell2
            Sheet sheet2 = workbook.getSheetAt(1);
            // 全局映射表：Map<行号, Map<列号, PictureData>>
            Map<Integer, Map<Integer, PictureData>> imageAnchorMap2 = new HashMap<>();
            imageAnchorMap2 = ExcelImageExtractor.buildImageAnchorMap(sheet2);
            List<RepresentativeAchievement> representativeAchievementList = new ArrayList<>();
            for (Row row : sheet2) {
                RepresentativeAchievement representativeAchievement = new RepresentativeAchievement();
                if (row.getRowNum() == 0) {
                    continue; // 跳过第一行
                }
                if (row == null) {
                    continue;
                }
                String theFirstColumn = ExcelImageUtils.getCellValueAsString(row.getCell(0));
                if (theFirstColumn.length() >= 2 && theFirstColumn.substring(0, 2).equals(TemplateCopywritingEnums.MSG.getMsg().substring(0, 2))) {
                    //遇到 注意文案
                    continue;
                }
                representativeAchievement.setCode(theFirstColumn); //第1列
//                if(StringUtils.isNotEmpty(representativeAchievement.getCode())){
                    // 提取图片并存储
                    PictureData picture2 = imageAnchorMap2.get(row.getRowNum()).get(1);// 第 2 列
                    if(ObjectUtils.isNotEmpty(picture2)){
                        //保存图片到服务器
                        String imageUrl2 = this.saveImageToServer(picture2.getData(), menuId);
                        representativeAchievement.setImg(imageUrl2);  //第二列
                    }
//                }
                representativeAchievement.setStandardCode(ExcelImageUtils.getCellValueAsString(row.getCell(2))); //第3列
//                if(StringUtils.isNotEmpty(representativeAchievement.getStandardCode())){
                    // 提取图片并存储
                    PictureData picture4 = imageAnchorMap2.get(row.getRowNum()).get(3); //第4列
                   if(ObjectUtils.isNotEmpty(picture4)){
                       //保存图片到服务器
                       String imageUrl4 = this.saveImageToServer(picture4.getData(), menuId);
                       representativeAchievement.setStandardImg(imageUrl4);
                   }
//                }
                representativeAchievement.setPatentCode(ExcelImageUtils.getCellValueAsString(row.getCell(4)));//第5列
//                if(StringUtils.isNotEmpty(representativeAchievement.getPatentCode())){
                    // 提取图片并存储
                    PictureData picture6 = imageAnchorMap2.get(row.getRowNum()).get(5); //第6列
                    if(ObjectUtils.isNotEmpty(picture6)){
                        //保存图片到服务器
                        String imageUrl6 = this.saveImageToServer(picture6.getData(), menuId);
                        representativeAchievement.setPatentImg(imageUrl6);
                    }
//                }
                representativeAchievement.setPaperCode(ExcelImageUtils.getCellValueAsString(row.getCell(6))); //第7列
//                if(StringUtils.isNotEmpty(representativeAchievement.getPaperCode())){
                    // 提取图片并存储
                    PictureData picture8 = imageAnchorMap2.get(row.getRowNum()).get(7); //第8列
                    if(ObjectUtils.isNotEmpty(picture8)){
                        //保存图片到服务器
                        String imageUrl8 = this.saveImageToServer(picture8.getData(), menuId);
                        representativeAchievement.setPaperImg(imageUrl8);
                    }
//                }
                representativeAchievementList.add(representativeAchievement);
            }
            achievementDataVo.setRepresentativeAchievementList(representativeAchievementList);

            //解析构建 shell3
            Sheet sheet3 = workbook.getSheetAt(2);
            // 全局映射表：Map<行号, Map<列号, PictureData>>
            Map<Integer, Map<Integer, PictureData>> imageAnchorMap3 = new HashMap<>();
            imageAnchorMap3 = ExcelImageExtractor.buildImageAnchorMap(sheet3);
            List<ResourceProtection> resourceProtectionList = new ArrayList<>();
            for (Row row : sheet3) {
                ResourceProtection resourceProtection = new ResourceProtection();
                if (row.getRowNum() == 0) {
                    continue; // 跳过第一行
                }
                if (row == null) {
                    continue;
                }
                String theFirstColumn = ExcelImageUtils.getCellValueAsString(row.getCell(0));
                if (theFirstColumn.length() >= 2 && theFirstColumn.substring(0, 2).equals(TemplateCopywritingEnums.MSG.getMsg().substring(0, 2))) {
                    //遇到 注意文案
                    continue;
                }
                resourceProtection.setCode(theFirstColumn); //第1列
                resourceProtection.setTitle(ExcelImageUtils.getCellValueAsString(row.getCell(1)));  //第2列
                // 提取图片并存储
                PictureData picture3 = imageAnchorMap3.get(row.getRowNum()).get(2);// 第 3 列
                if(ObjectUtils.isNotEmpty(picture3)){
                    String imageUrl3 = this.saveImageToServer(picture3.getData(), menuId);
                    resourceProtection.setImg(imageUrl3);
                }
                resourceProtection.setIntroduce(ExcelImageUtils.getCellValueAsString(row.getCell(3)));//第4列
                resourceProtectionList.add(resourceProtection);
            }
            achievementDataVo.setResourceProtectionList(resourceProtectionList);
        }catch (Exception e){
            log.error("constructAchievementVo,err:"+e.getMessage());
            throw new BusinessException(BusinessCodeEnums.BASE_COMMENT_FORMAT_ERR.getKey(),BusinessCodeEnums.BASE_COMMENT_FORMAT_ERR.getMsg());
        }
        return achievementDataVo;
    }

    /**
     * 科研团队填充数据
     * @param file
     * @param menuId
     * @return
     * @throws IOException
     */
    public TeamDataVo constructKeyTeamDataVo(MultipartFile file, Integer menuId) {
        TeamDataVo teamDataVos = new TeamDataVo();
        try {
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        //解析构建 shell1
        Sheet sheet1 = workbook.getSheetAt(0);
        ResearchteamIntroduce researchteamIntroduce = new ResearchteamIntroduce();
        //第1列，第2行
        researchteamIntroduce.setIntroduce(sheet1.getRow(1).getCell(0).getStringCellValue());
        teamDataVos.setResearchteamIntroduce(researchteamIntroduce);
        //解析构建 shell2
        Sheet sheet2 = workbook.getSheetAt(1);
        // 全局映射表：Map<行号, Map<列号, PictureData>>
        Map<Integer, Map<Integer, PictureData>> imageAnchorMap2 = new HashMap<>();
        imageAnchorMap2 = ExcelImageExtractor.buildImageAnchorMap(sheet2);
        List<TeamLeader> teamLeaderList = new ArrayList<>();
        for (Row row : sheet2) {
            TeamLeader teamLeader = new TeamLeader();
            if (row.getRowNum() == 0) {
                continue; // 跳过第一行
            }
            if (row == null) {
                continue;
            }
            String theFirstColumn = ExcelImageUtils.getCellValueAsString(row.getCell(0));
            if (theFirstColumn.length() >= 2 && theFirstColumn.substring(0, 2).equals(TemplateCopywritingEnums.MSG.getMsg().substring(0, 2))) {
                //遇到 注意文案
                continue;
            }
            teamLeader.setCode(theFirstColumn); //第一列
            teamLeader.setName(row.getCell(1).getStringCellValue());  //第二列
            teamLeader.setTitle(row.getCell(2).getStringCellValue()); //第三列
            // 提取图片并存储
            int rowNum = row.getRowNum();
            int targetCol = 3;  // 第 4 列
            PictureData picture = imageAnchorMap2.get(rowNum).get(targetCol);
            //保存图片到服务器
            String imageUrl = this.saveImageToServer(picture.getData(), menuId);
            teamLeader.setImg(imageUrl);  //第二列
            teamLeader.setIntroduce(ExcelImageUtils.getCellValueAsString(row.getCell(4)));
            //排序号
            teamLeader.setSortNum(rowNum);
            teamLeaderList.add(teamLeader);
        }
        teamDataVos.setTeamLeaders(teamLeaderList);
        //解析构建 shell3
        Sheet sheet3 = workbook.getSheetAt(2);
        // 全局映射表：Map<行号, Map<列号, PictureData>>
        Map<Integer, Map<Integer, PictureData>> imageAnchorMap3 = new HashMap<>();
        imageAnchorMap3 = ExcelImageExtractor.buildImageAnchorMap(sheet3);
        List<KeyMembers> keyMembersList = new ArrayList<>();
        for (Row row : sheet3) {
            KeyMembers keyMembers = new KeyMembers();
            if (row.getRowNum() == 0) {
                continue; // 跳过第一行
            }
            if (row == null) {
                continue;
            }
            String theFirstColumn = ExcelImageUtils.getCellValueAsString(row.getCell(0));
            if (theFirstColumn.length() >= 2 && theFirstColumn.substring(0, 2).equals(TemplateCopywritingEnums.MSG.getMsg().substring(0, 2))) {
                //遇到 注意文案
                continue;
            }
            keyMembers.setCode(theFirstColumn); //第一列
            keyMembers.setName(row.getCell(1).getStringCellValue());  //第二列
            keyMembers.setTitle(row.getCell(2).getStringCellValue()); //第三列
            // 提取图片并存储
            int rowNum = row.getRowNum();
            log.debug("rowNum:"+rowNum);
            int targetCol = 3;  // 第 4 列
            PictureData picture = imageAnchorMap3.get(rowNum).get(targetCol);
            String imageUrl = this.saveImageToServer(picture.getData(), menuId);
            keyMembers.setImg(imageUrl);  //第二列
            keyMembers.setIntroduce(ExcelImageUtils.getCellValueAsString(row.getCell(4)));
            //排序号
            keyMembers.setSortNum(rowNum);
            keyMembersList.add(keyMembers);
        }
        teamDataVos.setKeyMembers(keyMembersList);
        }catch (Exception e){
            log.error("constructKeyTeamDataVo,err:"+e.getMessage());
            throw new BusinessException(BusinessCodeEnums.BASE_COMMENT_FORMAT_ERR.getKey(),BusinessCodeEnums.BASE_COMMENT_FORMAT_ERR.getMsg());
        }
        return teamDataVos;
    }

//    /**
//     * 存储图片到服务器，根据菜单id对应存储到相应目录
//     * @param imageData
//     * @return 文件目录，文件访问url
//     * @throws IOException
//     */
//    private String saveImageToServer(byte[] imageData,Integer menuId) throws IOException {
//        // 检测图片格式（示例简略实现）
//        String fileExtension = ".png";
//        if (imageData.length > 3 && imageData[0] == (byte)0xFF && imageData[1] == (byte)0xD8) {
//            fileExtension = ".jpg";
//        }
//
//        // 生成唯一文件名
//        String fileName = UUID.randomUUID() + fileExtension;
//        Path savePath = Paths.get(SysValueSetEnums.TEMP_IMG_CATALOGUE.getValue()+TemplateCodeEnums.getById(menuId).getImportUrl(), fileName);
//
//        // 确保目录存在并保存文件
//        Files.createDirectories(savePath.getParent());
//        Files.write(savePath, imageData);
//
//        // 返回访问 URL（需配置服务器静态资源映射）
//        return String.valueOf(savePath);
//    }

//    private String saveImageToServer(PictureData picture, String filename) throws IOException {
//        byte[] data = picture.getData();
//        Path path = Paths.get(IMAGE_UPLOAD_DIR + filename);
//        Files.write(path, data);
//        return "/uploads/" + filename; // 返回可访问的URL路径
//    }
//    private String getImageExtension(PictureData picture) {
//        switch (picture.getPictureType()) {
//            case Workbook.PICTURE_TYPE_JPEG:
//                return ".jpg";
//            case Workbook.PICTURE_TYPE_PNG:
//                return ".png";
//            // 其他图片类型...
//            default:
//                return ".dat";
//        }
//    }


    
    /**
     * 科研团队模板数据
     * @param response
     * @param menuId
     */

    public void researchteamIntroduceXSSFWorkbook(HttpServletResponse response, Integer menuId,DataTemplate dataTemplate) {
        try {
            //查询对应数据
            TeamDataVo teamDataVo=this.queryTeamLeader(menuId);
            XSSFWorkbook workbook = new XSSFWorkbook();
            // Sheet 1: 科员团队介绍
            createEmployeeSheet(workbook, teamDataVo.getResearchteamIntroduce());
            // Sheet 2: 团队带头人
            createTeamLeaderSheet(workbook,teamDataVo.getTeamLeaders());
            // Sheet 3: 骨干成员
            createKeyMembersSheet(workbook,teamDataVo.getKeyMembers());
//            // 3. 设置响应头
//            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//            String encodedFileName = URLEncoder.encode(dataTemplate.getTemplateName()+".xlsx", StandardCharsets.UTF_8);
//            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
//            // 4. 写入响应流
//            workbook.write(response.getOutputStream());
//            workbook.close();
            //处理流
            setContentLength(response,workbook,dataTemplate);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 科研团队模板-科员团队介绍
     * @param workbook
     * @param researchteamIntroduce
     * @throws SQLException
     */
    private static void createEmployeeSheet(XSSFWorkbook workbook, ResearchteamIntroduce researchteamIntroduce) throws SQLException {
        XSSFSheet sheet = workbook.createSheet("团队介绍");
        // 创建表头
        String[] headers = {"团队介绍"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        //数据填充
        if(ObjectUtils.isNotEmpty(researchteamIntroduce)){
            XSSFRow row = sheet.createRow( 1);
            row.createCell(0).setCellValue(researchteamIntroduce.getIntroduce());
        }
    }

    /**
     * 科研团队模板-团队带头人
     * @param workbook
     * @param teamLeaderList
     * @throws SQLException
     */
    private  void createTeamLeaderSheet(XSSFWorkbook workbook,  List<TeamLeader> teamLeaderList) throws SQLException {
        XSSFSheet sheet = workbook.createSheet("团队带头人");
        Integer numSum=TemplateCopywritingEnums.MSG.getKey();
        // 创建表头 人员编码	姓名	头衔	照片	介绍
        String[] headers = {"人员编码","姓名","头衔","照片","介绍"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        if(CollectionUtil.isNotEmpty(teamLeaderList)){
            numSum=numSum+teamLeaderList.size();
            //数据填充
            for (int i=0;i<teamLeaderList.size();i++){
                XSSFRow row = sheet.createRow(i + 1);  // 第一行是表头，所以数据从第2行开始
                row.createCell(0).setCellValue(teamLeaderList.get(i).getCode());
                row.createCell(1).setCellValue(teamLeaderList.get(i).getName());
                row.createCell(2).setCellValue(teamLeaderList.get(i).getTitle());
                String imgUrl= teamLeaderList.get(i).getImg();
                this.imgDisplacement(workbook,sheet,imgUrl,3,i+1,1,1);
                row.createCell(4).setCellValue(teamLeaderList.get(i).getIntroduce());
            }
        }

        XSSFRow row = sheet.createRow(numSum);  // 第一行是表头，所以数据从第2行开始
        // 创建单元格并设置"注意"文本
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(TemplateCopywritingEnums.MSG.getMsg());
        redFontStyle(cell,workbook);
    }

    /**
     * 科研团队模板-骨干成员
     * @param workbook
     * @param keyMembersList
     * @throws SQLException
     */
    private  void createKeyMembersSheet(XSSFWorkbook workbook,  List<KeyMembers> keyMembersList) throws SQLException {
        XSSFSheet sheet = workbook.createSheet("骨干成员");
        Integer numSum=TemplateCopywritingEnums.MSG.getKey();
        // 创建表头 人员编码	姓名	头衔	照片	介绍
        String[] headers = {"人员编码","姓名","头衔","照片","介绍"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        if(CollectionUtil.isNotEmpty(keyMembersList)){
            numSum=numSum+keyMembersList.size();
            //数据填充
            for (int i=0;i<keyMembersList.size();i++){
                XSSFRow row = sheet.createRow(i + 1);  // 第一行是表头，所以数据从第2行开始
                row.createCell(0).setCellValue(keyMembersList.get(i).getCode());
                row.createCell(1).setCellValue(keyMembersList.get(i).getName());
                row.createCell(2).setCellValue(keyMembersList.get(i).getTitle());
//                row.createCell(3).setCellValue(keyMembersList.get(i).getImg());
                String imgUrl= keyMembersList.get(i).getImg();
                this.imgDisplacement(workbook,sheet,imgUrl,3,i+1,1,1);
                row.createCell(4).setCellValue(keyMembersList.get(i).getIntroduce());
            }
        }
        XSSFRow row = sheet.createRow(numSum);  // 第一行是表头，所以数据从第2行开始
        row.createCell(0).setCellValue(TemplateCopywritingEnums.MSG.getMsg());
        // 创建单元格并设置"注意"文本
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(TemplateCopywritingEnums.MSG.getMsg());
        redFontStyle(cell,workbook);
    }


    /**
     * 科研成果模板数据
     * @param response
     * @param menuId
     */
    private void researchAchievementsXSSFWorkbook(HttpServletResponse response, Integer menuId,DataTemplate dataTemplate) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            AchievementDataVo achievementDataVo=this.queryAchievement(menuId);
            // Sheet 1: 成果简介
            createAchievementIntroduceSheet(workbook, achievementDataVo.getAchievementIntroduce());
            // Sheet 2: 代表性成果
            createRepresentativeAchievementSheet(workbook,achievementDataVo.getRepresentativeAchievementList());
            // Sheet 3: 资源保护
            createResourceProtectionSheet(workbook,achievementDataVo.getResourceProtectionList());
//            // 3. 设置响应头
//            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//            String encodedFileName = URLEncoder.encode(dataTemplate.getTemplateName()+".xlsx", StandardCharsets.UTF_8);
//            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
//            // 4. 写入响应流
//            workbook.write(response.getOutputStream());
//            workbook.close();
            //处理流
            setContentLength(response,workbook,dataTemplate);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 科研成果-成果简介
     * @param workbook
     * @param achievementIntroduce
     */
    private void createAchievementIntroduceSheet(XSSFWorkbook workbook, AchievementIntroduce achievementIntroduce) {
        XSSFSheet sheet = workbook.createSheet("成果简介");
        // 创建表头
        String[] headers = {"成果简介"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        //数据填充
        XSSFRow row = sheet.createRow( 1);
        row.createCell(0).setCellValue(achievementIntroduce.getIntroduce());
    }

    /**
     * 科研成果-代表性成果
     * @param workbook
     * @param representativeAchievementList
     */
    private void createRepresentativeAchievementSheet(XSSFWorkbook workbook, List<RepresentativeAchievement> representativeAchievementList) {
        XSSFSheet sheet = workbook.createSheet("代表性成果");
        Integer numSum=TemplateCopywritingEnums.MSG.getKey();
        // 创建表头  成果编码	获奖成果图片	标准编码	代表性标准图片	专利编码	代表性专利图片	论文编码	代表性论文图片
        String[] headers = {"成果编码","获奖成果图片","标准编码","代表性标准图片","专利编码","代表性专利图片","论文编码","代表性论文图片"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        if(CollectionUtil.isNotEmpty(representativeAchievementList)){
            numSum=numSum+representativeAchievementList.size();
            //数据填充
            for (int i=0;i<representativeAchievementList.size();i++){
                XSSFRow row = sheet.createRow(i + 1);  // 第一行是表头，所以数据从第2行开始
                row.createCell(0).setCellValue(representativeAchievementList.get(i).getCode());
//                row.createCell(1).setCellValue(representativeAchievementList.get(i).getImg());
                String imgUrl1= representativeAchievementList.get(i).getImg();
                this.imgDisplacement(workbook,sheet,imgUrl1,1,i+1,1,1);

                row.createCell(2).setCellValue(representativeAchievementList.get(i).getStandardCode());
//                row.createCell(3).setCellValue(representativeAchievementList.get(i).getStandardImg());
                String imgUrl2= representativeAchievementList.get(i).getStandardImg();
                this.imgDisplacement(workbook,sheet,imgUrl2,3,i+1,1,1);

                row.createCell(4).setCellValue(representativeAchievementList.get(i).getPatentCode());
//                row.createCell(5).setCellValue(representativeAchievementList.get(i).getPatentImg());
                String imgUrl3= representativeAchievementList.get(i).getPatentImg();
                this.imgDisplacement(workbook,sheet,imgUrl3,5,i+1,1,1);

                row.createCell(6).setCellValue(representativeAchievementList.get(i).getPaperCode());
//                row.createCell(7).setCellValue(representativeAchievementList.get(i).getPaperImg());
                String imgUrl4= representativeAchievementList.get(i).getPaperImg();
                this.imgDisplacement(workbook,sheet,imgUrl4,7,i+1,1,1);
            }
        }
        XSSFRow row = sheet.createRow(numSum);  // 第一行是表头，所以数据从第2行开始
        // 创建单元格并设置"注意"文本
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(TemplateCopywritingEnums.MSG.getMsg());
        redFontStyle(cell,workbook);
    }

    /**
     * 科研成果-资源保护
     *
     * @param workbook
     * @param resourceProtectionList
     */
    private void createResourceProtectionSheet(XSSFWorkbook workbook, List<ResourceProtection> resourceProtectionList) {
        Integer numSum=TemplateCopywritingEnums.MSG.getKey();
        XSSFSheet sheet = workbook.createSheet("资源保护");
        // 创建表头  编码	标题	图片	介绍
        String[] headers = {"编码", "标题", "图片", "介绍"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        if(CollectionUtil.isNotEmpty(resourceProtectionList)){
            numSum=numSum+resourceProtectionList.size();
            //数据填充
            for (int i = 0; i < resourceProtectionList.size(); i++) {
                XSSFRow row = sheet.createRow(i + 1);  // 第一行是表头，所以数据从第2行开始
                row.createCell(0).setCellValue(resourceProtectionList.get(i).getCode());
                row.createCell(1).setCellValue(resourceProtectionList.get(i).getTitle());
//                row.createCell(2).setCellValue(resourceProtectionList.get(i).getImg());
                String imgUrl= resourceProtectionList.get(i).getImg();
                this.imgDisplacement(workbook,sheet,imgUrl,2,i+1,1,1);
                row.createCell(3).setCellValue(resourceProtectionList.get(i).getIntroduce());
            }
        }

        XSSFRow row = sheet.createRow(numSum);  // 第一行是表头，所以数据从第2行开始
        row.createCell(0).setCellValue(TemplateCopywritingEnums.MSG.getMsg());
        // 创建单元格并设置"注意"文本
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(TemplateCopywritingEnums.MSG.getMsg());
        redFontStyle(cell,workbook);
    }


    /**
     * 地方猪种源基地
     * @param response
     * @param menuId
     */
    private void baseXSSFWorkbook(HttpServletResponse response, Integer menuId,DataTemplate dataTemplate) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            // Sheet 1: 种源基地数据更新模板
            List<BaseTemplate> baseTemplateList=baseInfoService.queryBaseInfoImg(menuId);
            createBaseSheet(workbook,baseTemplateList);
//            // 3. 设置响应头
//            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//            String encodedFileName = URLEncoder.encode(dataTemplate.getTemplateName()+".xlsx", StandardCharsets.UTF_8);
//            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
//            // 4. 写入响应流
//            workbook.write(response.getOutputStream());
//            workbook.close();
            //处理流
            setContentLength(response,workbook,dataTemplate);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 地方猪种源基地
     * @param workbook
     * @param baseTemplateList
     */
    private void createBaseSheet(XSSFWorkbook workbook, List<BaseTemplate> baseTemplateList) {
        Integer numSum=TemplateCopywritingEnums.MSG.getKey();
        XSSFSheet sheet = workbook.createSheet("种源基地数据更新模板");
        // 创建表头  基地编码	基地名称	猪只品种	基地照片	基地介绍
        String[] headers = {"基地编码","基地名称","猪只品种","基地照片","基地介绍"};
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        if(CollectionUtil.isNotEmpty(baseTemplateList)){
            numSum=numSum+baseTemplateList.size();
            //数据填充
            for (int i=0;i<baseTemplateList.size();i++){
                XSSFRow row = sheet.createRow(i + 1);  // 第一行是表头，所以数据从第2行开始
                row.createCell(0).setCellValue(baseTemplateList.get(i).getCode());
                row.createCell(1).setCellValue(baseTemplateList.get(i).getBaseName());
                row.createCell(2).setCellValue(baseTemplateList.get(i).getVarietyName());
//                row.createCell(3).setCellValue(baseTemplateList.get(i).getImg());
                String imgUrl= baseTemplateList.get(i).getImg();
                this.imgDisplacement(workbook,sheet,imgUrl,3,i+1,1,1);
                row.createCell(4).setCellValue(baseTemplateList.get(i).getBaseInfo());
            }
        }
        XSSFRow row = sheet.createRow(numSum);  // 第一行是表头，所以数据从第2行开始
        // 创建单元格并设置"注意"文本
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(TemplateCopywritingEnums.BASE_MSG.getMsg());
        redFontStyle(cell,workbook);
    }

    /**
     * 红色字体
     * @param cell
     * @param workbook
     */
    private void redFontStyle( XSSFCell cell,XSSFWorkbook workbook){
        // 创建红色字体样式
        XSSFCellStyle redStyle = workbook.createCellStyle();
        XSSFFont redFont = workbook.createFont();
        redFont.setColor(IndexedColors.RED.getIndex());  // 设置为红色
        redStyle.setFont(redFont);
        // 应用红色样式到单元格
        cell.setCellStyle(redStyle);
    }

    /**
     * Excel 表格浮动位移
     * @param workbook 位移表格对象
     * @param sheet 位移表格对象
     * @param imgUrl 位移图片url
     * @param col 位移列数
     * @param row 位移行数
     * @param widthlnCells 图片跨列数
     * @param heightlnCells 图片跨行数
     */
    private void imgDisplacement(XSSFWorkbook workbook, XSSFSheet sheet,String imgUrl,int col,int row,int widthlnCells,int heightlnCells){
        try{
            if(StringUtils.isEmpty(imgUrl)){
                log.warn("imgDisplacement,warn:获取不到图片,图片地址url:"+imgUrl);
                return;
            }
//            String tempImgCataloguePrefixHead=sysValueSetService.queryPriorityValueByCode(SysValueSetEnums.TEMP_IMG_CATALOGUE_PREFIX_HEAD.getCode());
//            String chageImgUrl= tempImgCataloguePrefixHead+imgUrl;
            log.info("imgUrl"+imgUrl);
            // 3. 加载图片文件
            // 对于实际应用，你可能需要从文件系统绝对路径加载
            FileSystemResource imgFile = new FileSystemResource(imgUrl);
            InputStream inputStream = imgFile.getInputStream();
            byte[] bytes = IOUtils.toByteArray(inputStream);
            int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
            inputStream.close();


            // 4. 创建绘图对象并添加图片
            CreationHelper helper = workbook.getCreationHelper();
            Drawing<?> drawing = sheet.createDrawingPatriarch();
            // 创建锚点 (指定图片位置)
            //定位 C4 单元格 2 3 跨2列1行
            workbook.setActiveSheet(0); // 激活第一个工作表
            ClientAnchor anchor = ExcelImageUtils.createImageAnchor(helper,col,row,widthlnCells,heightlnCells);
            // 添加图片
            drawing.createPicture(anchor, pictureIdx);
        }catch (Exception e){
            e.printStackTrace();
            log.error("imgDisplacement,err:"+e.getMessage());
        }
    }

    /**
     * 存储图片到服务器，根据菜单id对应存储到相应目录
     * @param imageData
     * @return 文件目录，文件访问url
     * @throws IOException
     */
    private  String  saveImageToServer(byte[] imageData,Integer menuId) throws IOException {
        // 检测图片格式（示例简略实现）
        String fileExtension = ".png";
        if (imageData.length > 3 && imageData[0] == (byte)0xFF && imageData[1] == (byte)0xD8) {
            fileExtension = ".jpg";
        }
        // 生成唯一文件名
        String fileName = UUID.randomUUID() + fileExtension;
        //获取图片前缀
        String tempImgCataloguePrefixHead=sysValueSetService.queryPriorityValueByCode(SysValueSetEnums.TEMP_IMG_CATALOGUE_PREFIX_HEAD.getCode());
        String tempImgCatalogue=sysValueSetService.queryPriorityValueByCode(SysValueSetEnums.TEMP_IMG_CATALOGUE.getCode());
        //图片路径：
        String imgUrl=tempImgCataloguePrefixHead+tempImgCatalogue;
        Path savePath = Paths.get(imgUrl+ TemplateCodeEnums.getById(menuId).getImportUrl(), fileName);
        log.info("savePath:"+String.valueOf(savePath));
        try{
            // 确保目录存在并保存文件
            Files.createDirectories(savePath.getParent());
            Files.write(savePath, imageData);
        }catch (IOException e){
            e.printStackTrace();
            log.error("saveImageToServer,err:"+e.getMessage());
        }
        // 返回访问 URL（需配置服务器静态资源映射）
        return String.valueOf(savePath);
    }

    /**
     * 设置 Content-Length 头部 :配合前端交互进度条
     * @param response
     * @param workbook
     * @param dataTemplate
     * @throws IOException
     */
    private void setContentLength(HttpServletResponse response,XSSFWorkbook workbook,DataTemplate dataTemplate) throws IOException {
        // 使用临时文件来获取文件大小:为了配合前端交互进度条
        File tempFile = File.createTempFile("export", ".xlsx");
        try (FileOutputStream fos = new FileOutputStream(tempFile)) {
            workbook.write(fos);
        }
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String encodedFileName = URLEncoder.encode(dataTemplate.getTemplateName() + ".xlsx", StandardCharsets.UTF_8);
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
        response.setContentLengthLong(tempFile.length()); // 使用setContentLengthLong支持大文件
        // 使用Files.copy进行高效文件传输
        try (InputStream in = new FileInputStream(tempFile);
             OutputStream out = response.getOutputStream()) {
            byte[] buffer = new byte[4096];
            int bytesRead;
            while ((bytesRead = in.read(buffer)) != -1) {
                out.write(buffer, 0, bytesRead);
            }
        }
        // 关闭资源并删除临时文件
        workbook.close();
        Files.deleteIfExists(tempFile.toPath());
    }

}




